package cn.org.intelli.worktime;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

public class Application {
    private static final int XLS_MAX_ROWS = 65535;
    private static final int XLSX_MAX_ROWS = 1048575;

    public static void main(String[] args) {
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy");
        String fileName = String.format("项目工作统计%s.xlsx", dateTimeFormatter.format(LocalDateTime.now()));
        System.out.println(fileName);
        File dictory = new File("/opt/worktime");
        if (dictory.isDirectory()) {
            List<File> files = Arrays.stream(dictory.listFiles()).filter(file -> file.isDirectory() == false && file.getName().equals(fileName) == false).collect(Collectors.toList());
            Arrays.stream(dictory.listFiles()).filter(file -> file.isDirectory() == false && file.getName().equals(fileName)).findFirst().ifPresent(mergedFile -> {
                try {
                    mergeExcel(mergedFile, files, true);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    /**
     * 合并多个 Excel 文件
     *
     * @param mergedFile 合并后的文件
     * @param files      待合并的文件
     * @param isXlsx     合并文件类型是否是 xlsx
     * @throws IOException 合并异常
     */
    private static void mergeExcel(File mergedFile, List<File> files, boolean isXlsx) throws IOException {
        if (mergedFile == null || files == null) {
            return;
        }
        try (Workbook mergedWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(mergedFile)) : new HSSFWorkbook(); FileOutputStream out = new FileOutputStream(mergedFile)) {
            Sheet sheet = mergedWorkbook.getSheetAt(0);
            int start = getVaildRows(sheet);
            for (File file : files) {
                if (file == null) {
                    continue;
                }
                try (Workbook oldWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file))) {
                    int oldSheetSize = 1;
                    for (int i = 0; i < oldSheetSize; i++) {
                        Sheet oldSheet = oldWorkbook.getSheetAt(i);
                        int oldRowSize = oldSheet.getLastRowNum();
                        for (int j = 1; j <= oldRowSize; j++) {
                            Row oldRow = oldSheet.getRow(j);
                            if (oldRow.getCell(0) != null && oldRow.getCell(0).getCellType() != CellType.BLANK) {
                                Row newRow = sheet.createRow(start);
                                copyRow(oldRow, newRow);
                                start++;
                            }
                        }
                    }
                }
            }
            mergedWorkbook.write(out);
        }
        files.stream().forEach(file -> {
            String bak = String.format("%s/bak/%s", file.getParentFile().getAbsolutePath(), file.getName());
            System.out.println(bak);
            file.renameTo(new File(bak));
        });
    }

    public static int getVaildRows(Sheet sheet) throws IOException {
        boolean flag = false;
        for (int i = 0; i <= sheet.getLastRowNum(); ) {
            Row r = sheet.getRow(i);
            if (r == null) {
                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                continue;
            }
            flag = false;
            if (r.getCell(0).getCellType() != CellType.BLANK) {
                flag = true;
            }
            if (flag) {
                i++;
                continue;
            } else {
                if (i == sheet.getLastRowNum()) {
                    sheet.removeRow(r);
                } else {
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                }
            }
        }
        return sheet.getLastRowNum() + 1;
    }

    private static void copyRow(Row oldRow, Row newRow) {
        newRow.setHeight(oldRow.getHeight());
        for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
            Cell oldCell = oldRow.getCell(i);
            if (null != oldCell) {
                copyCell(oldCell, newRow.createCell(i));
            }
        }
    }

    private static void copyCell(Cell oldCell, Cell newCell) {
        switch (oldCell.getCellType()) {
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BLANK:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            default:
                break;
        }
    }
}
